﻿using System;
using System.Collections.Generic;
using Microsoft.TeamFoundation.WorkItemTracking.Client;
using Microsoft.TeamFoundation.Client;

namespace AvisoSuporte.Communication
{
    public class Geral : IDisposable
    {
        #region Variáveis Públicas

        WorkItemStore WIStore;
        TeamFoundationServer tfs;

        #endregion

        #region Propriedades Públicas

        public string Usuario
        {
            get;
            set;
        }

        public string Senha
        {
            get;
            set;
        }

        #endregion

        #region Construtor

        public Geral()
        {

        }

        #endregion

        #region Métodos Públicos

        public List<Model.WorkItem> RetornaTasksSiteBmfBovespa()
        {
            CDSBNetworkCredentials Credentials = new CDSBNetworkCredentials(Usuario, Senha);

            this.tfs = TeamFoundationServerFactory.GetServer(("http://utilcorp05:8080"), Credentials);
            this.WIStore = (WorkItemStore)tfs.GetService(typeof(WorkItemStore));

            WorkItemCollection itens = WIStore.Query(@"SELECT [System.Id], [System.Title], [System.AssignedTo], 
            [Microsoft.VSTS.Scheduling.FinishDate], [Bovespa.Sinacor.HorasPrevistas], [Bovespa.Sinacor.HorasRealizadas], [Microsoft.VSTS.Scheduling.StartDate], 
            [Bovespa.Sinacor.TerminoRealizado], [Bovespa.Sinacor.InicioRealizado],  [Microsoft.VSTS.Common.Priority]
            FROM WorkItems WHERE 
            [System.TeamProject] = 'SiteBMFBovespa'  
            AND  [System.WorkItemType] = 'Task'  
            AND ( [System.AssignedTo] = 'Joao Paulo Gregório Pontes'  
            OR  [System.AssignedTo] = 'Carlos Alberto Gonçalves Carvalho'  
            OR  [System.AssignedTo] = 'Marcelo Luis Prudente do Amaral') AND [System.State] = 'Ativa' ORDER BY [System.Id]");

            List<Model.WorkItem> lista = new List<Model.WorkItem>();

            foreach (WorkItem workItem in itens)
            {
                Model.WorkItem demanda = new Model.WorkItem();
                demanda.ID = workItem.Id;
                demanda.AssignedTo = workItem.Fields.Contains("Assigned To") && workItem.Fields["Assigned To"].Value != null ? workItem.Fields["Assigned To"].Value.ToString() : "";
                demanda.WorkItemType = workItem.Type.Name;
                if (workItem.Fields.Contains("Start Date") && workItem.Fields["Start Date"].Value != null)
                    demanda.StartDate = (DateTime)workItem.Fields["Start Date"].Value;
                if (workItem.Fields.Contains("Finish Date") && workItem.Fields["Finish Date"].Value != null)
                    demanda.FinishDate = (DateTime)workItem.Fields["Finish Date"].Value;
                demanda.Title = workItem.Title;
                demanda.ChangedDate = workItem.ChangedDate;
                demanda.Remedy = workItem.Fields["Priority"].Value != null ? workItem.Fields["Priority"].Value.ToString() : "";
                demanda.Description = workItem.Description;
                demanda.CreatedBy = workItem.CreatedBy;
                demanda.CreatedDate = workItem.CreatedDate;

                if (workItem.Fields.Contains("Inicio Realizado") && workItem.Fields["Inicio Realizado"].Value != null)
                    demanda.StartDateRealized = (DateTime)workItem.Fields["Inicio Realizado"].Value;
                if (workItem.Fields.Contains("Termino Realizado") && workItem.Fields["Termino Realizado"].Value != null)
                    demanda.FinishDateRealized = (DateTime)workItem.Fields["Termino Realizado"].Value;

                if (workItem.Fields.Contains("Horas Previstas") && workItem.Fields["Horas Previstas"].Value != null)
                    demanda.HoursPlanned = Convert.ToDouble(workItem.Fields["Horas Previstas"].Value.ToString());

                if (workItem.Fields.Contains("Horas Realizadas") && workItem.Fields["Horas Realizadas"].Value != null)
                    demanda.HoursRealized = Convert.ToDouble(workItem.Fields["Horas Realizadas"].Value.ToString());

                if (workItem.Fields.Contains("Remedy") && workItem.Fields["Remedy"].Value != null)
                    demanda.Remedy = workItem.Fields["Remedy"].Value.ToString();

                demanda.CreatedBy = workItem.CreatedBy;
                if (workItem.Fields.Contains("Tecnologia"))
                    demanda.Technology = workItem.Fields["Tecnologia"].Value.ToString();
                else
                    demanda.Technology = "Não definida";
                demanda.State = workItem.State;

                lista.Add(demanda);

            }

            this.tfs.Dispose();
            return lista;
        }

        public List<Model.WorkItem> RetornaBugsCorrigidosDia(string data_inicio, string data_final)
        {
            CDSBNetworkCredentials Credentials = new CDSBNetworkCredentials(Usuario, Senha);

            this.tfs = TeamFoundationServerFactory.GetServer(("http://utilcorp05:8080"), Credentials);
            this.WIStore = (WorkItemStore)tfs.GetService(typeof(WorkItemStore));

            WorkItemCollection itens = WIStore.Query(@"SELECT [System.Id], [System.AreaPath], [System.WorkItemType], [System.State], [System.AssignedTo], [System.Title], 
                         [Microsoft.VSTS.Scheduling.StartDate], [Microsoft.VSTS.Scheduling.FinishDate], [Microsoft.VSTS.Common.Rank], [System.ChangedDate], [Bovespa.Sinacor.Remedy]
                  FROM WorkItems 
                        
                           WHERE [System.WorkItemType] = 'Bug'  
                            AND  [Bovespa.Sinacor.Origem] IN ('Produção', 'Corretoras')  
                            AND ( [System.State] NOT CONTAINS 'Integrado' 
                            AND  [System.State] NOT CONTAINS 'Encerrado'  
                            AND  [System.State] NOT CONTAINS 'Cancelado' ) 
                            AND  [System.ChangedDate] >= '" + data_inicio + @"'
                            AND  [System.ChangedDate] <= '" + data_final + @"'
                        ");

            List<Model.WorkItem> lista = new List<Model.WorkItem>();

            foreach (WorkItem workItem in itens)
            {
                Model.WorkItem demanda = new Model.WorkItem();
                demanda.ID = workItem.Id;
                demanda.AreaPath = workItem.AreaPath;
                demanda.AssignedTo = workItem.Fields.Contains("Assigned To") && workItem.Fields["Assigned To"].Value != null ? workItem.Fields["Assigned To"].Value.ToString() : "";
                demanda.WorkItemType = workItem.Type.Name;
                if (workItem.Fields.Contains("Start Date") && workItem.Fields["Start Date"].Value != null)
                    demanda.StartDate = (DateTime)workItem.Fields["Start Date"].Value;
                if (workItem.Fields.Contains("Finish Date") && workItem.Fields["Finish Date"].Value != null)
                    demanda.FinishDate = (DateTime)workItem.Fields["Finish Date"].Value;

                demanda.Title = workItem.Title;
                demanda.State = workItem.State;
                demanda.Rank = workItem.Fields["Rank"].Value.ToString();
                demanda.ChangedDate = workItem.ChangedDate;
                demanda.Remedy = workItem.Fields["Remedy"].Value != null ? workItem.Fields["Remedy"].Value.ToString() : "";

                if (workItem.Fields.Contains("Inicio Realizado") && workItem.Fields["Inicio Realizado"].Value != null)
                    demanda.StartDateRealized = (DateTime)workItem.Fields["Inicio Realizado"].Value;
                if (workItem.Fields.Contains("Termino Realizado") && workItem.Fields["Termino Realizado"].Value != null)
                    demanda.FinishDateRealized = (DateTime)workItem.Fields["Termino Realizado"].Value;

                if (workItem.Fields.Contains("Horas Previstas") && workItem.Fields["Horas Previstas"].Value != null)
                    demanda.HoursPlanned = Convert.ToDouble(workItem.Fields["Horas Previstas"].Value.ToString());

                if (workItem.Fields.Contains("Horas Realizadas") && workItem.Fields["Horas Realizadas"].Value != null)
                    demanda.HoursRealized = Convert.ToDouble(workItem.Fields["Horas Realizadas"].Value.ToString());

                lista.Add(demanda);
            }

            this.tfs.Dispose();
            return lista;
        }

        public List<Model.WorkItem> RetornaBugsDia(string data_inicio)
        {
            CDSBNetworkCredentials Credentials = new CDSBNetworkCredentials(Usuario, Senha);

            this.tfs = TeamFoundationServerFactory.GetServer(("http://utilcorp05:8080"), Credentials);
            this.WIStore = (WorkItemStore)tfs.GetService(typeof(WorkItemStore));

            WorkItemCollection itens = WIStore.Query(@"SELECT [System.Id], [System.AreaPath], [System.WorkItemType], [System.State], [System.AssignedTo], [System.Title], 
                         [Microsoft.VSTS.Scheduling.StartDate], [Microsoft.VSTS.Scheduling.FinishDate], [Microsoft.VSTS.Common.Rank], [System.ChangedDate], [Bovespa.Sinacor.Remedy]
                  FROM WorkItems 
                        
                           WHERE [System.WorkItemType] = 'Bug'  
                            AND  [Bovespa.Sinacor.Origem] IN ('Produção', 'Corretoras')  
                            AND  ([Microsoft.VSTS.Scheduling.FinishDate] >= '" + data_inicio + @"'
                            OR( [System.State] in ('Ativo','Analise','Codificacao','Codificado')) 
                            AND ( [System.AssignedTo] = 'Joao Paulo Gregório Pontes'  
                            OR  [System.AssignedTo] = 'Carlos Alberto Gonçalves Carvalho'  
                            OR  [System.AssignedTo] = 'Marcelo Luis Prudente do Amaral' ) 
                            )
                        ");

            List<Model.WorkItem> lista = new List<Model.WorkItem>();

            foreach (WorkItem workItem in itens)
            {
                Model.WorkItem demanda = new Model.WorkItem();
                demanda.ID = workItem.Id;
                demanda.AreaPath = workItem.AreaPath;
                demanda.AssignedTo = workItem.Fields.Contains("Assigned To") && workItem.Fields["Assigned To"].Value != null ? workItem.Fields["Assigned To"].Value.ToString() : "";
                demanda.WorkItemType = workItem.Type.Name;
                if (workItem.Fields.Contains("Start Date") && workItem.Fields["Start Date"].Value != null)
                    demanda.StartDate = (DateTime)workItem.Fields["Start Date"].Value;
                if (workItem.Fields.Contains("Finish Date") && workItem.Fields["Finish Date"].Value != null)
                    demanda.FinishDate = (DateTime)workItem.Fields["Finish Date"].Value;

                demanda.Title = workItem.Title;
                demanda.State = workItem.State;
                demanda.Rank = workItem.Fields["Rank"].Value.ToString();
                demanda.ChangedDate = workItem.ChangedDate;
                demanda.Remedy = workItem.Fields["Remedy"].Value != null ? workItem.Fields["Remedy"].Value.ToString() : "";
                demanda.TeamProject = workItem.Project.Name;
                demanda.CreatedBy = workItem.CreatedBy;
                demanda.CreatedDate = workItem.CreatedDate;

                demanda.FatorGerador = (workItem.Fields.Contains("Gerador") && workItem.Fields["Gerador"].Value != null ? workItem.Fields["Gerador"].Value.ToString() : string.Empty);

                if (workItem.Fields.Contains("Inicio Realizado") && workItem.Fields["Inicio Realizado"].Value != null)
                    demanda.StartDateRealized = (DateTime)workItem.Fields["Inicio Realizado"].Value;
                if (workItem.Fields.Contains("Termino Realizado") && workItem.Fields["Termino Realizado"].Value != null)
                    demanda.FinishDateRealized = (DateTime)workItem.Fields["Termino Realizado"].Value;

                if (workItem.Fields.Contains("Horas Previstas") && workItem.Fields["Horas Previstas"].Value != null)
                    demanda.HoursPlanned = Convert.ToDouble(workItem.Fields["Horas Previstas"].Value.ToString());

                if (workItem.Fields.Contains("Horas Realizadas") && workItem.Fields["Horas Realizadas"].Value != null)
                    demanda.HoursRealized = Convert.ToDouble(workItem.Fields["Horas Realizadas"].Value.ToString());

                foreach (Attachment attach in workItem.Attachments) 
                {
                    if(attach.Name.ToLower().IndexOf("pe.txt") >= 0)
                        demanda.PendenciaExterna = true;
                }

                foreach(Revision rev in workItem.Revisions)
                {

                    if (rev.Fields["State"].Value.ToString().ToLower() == "codificacao") 
                    {
                        demanda.CorrigidoPor = rev.Fields["Assigned To"].Value.ToString();
                        
                    }

                    if(rev.Fields["Reason"].Value.ToString().ToLower() == "é um caso" ||
                        rev.Fields["Reason"].Value.ToString().ToLower() == "foi reproduzido" ||
                        rev.Fields["Reason"].Value.ToString().ToLower() == "não é duplicado" ||
                        rev.Fields["Reason"].Value.ToString().ToLower() == "não é obsoleto" ||
                        rev.Fields["Reason"].Value.ToString().ToLower() == "não foi corrigido" ||
                        rev.Fields["Reason"].Value.ToString().ToLower() == "não pode ser suspenso" ||
                        rev.Fields["Reason"].Value.ToString().ToLower() == "tem que ser corrigido")
                    {
                        demanda.NaoCorrigido = true;
                        demanda.DataNaoCorrigido = Convert.ToDateTime(rev.Fields["Changed Date"].Value);
                    }

                    if (rev.Fields["State"].Value.ToString().ToLower() == "cancelado")
                    {
                        demanda.Cancelado = true;
                        demanda.DataCancelamento = Convert.ToDateTime(rev.Fields["Changed Date"].Value);
                    }
                }
                
                lista.Add(demanda);
            }

            this.tfs.Dispose();
            return lista;
        }

        public List<Model.WorkItem> RetornaBugsMes(DateTime data_inicio, DateTime data_fim)
        {
            CDSBNetworkCredentials Credentials = new CDSBNetworkCredentials(Usuario, Senha);

            this.tfs = TeamFoundationServerFactory.GetServer(("http://utilcorp05:8080"), Credentials);
            this.WIStore = (WorkItemStore)tfs.GetService(typeof(WorkItemStore));

            WorkItemCollection itens = WIStore.Query(@"SELECT [Bovespa.Sinacor.Origem], [System.Id],
                                                              [System.Title], [System.State],
                                                              [System.AssignedTo], [System.AreaPath],
                                                              [Microsoft.VSTS.Scheduling.StartDate], [Microsoft.VSTS.Scheduling.FinishDate],
                                                              [Bovespa.Sinacor.TerminoRealizado], [Bovespa.Sinacor.HorasPrevistas],
                                                              [Bovespa.Sinacor.HorasRealizadas]
                                                         FROM WorkItems
                                                        WHERE [Bovespa.Sinacor.Origem] IN ('Corretoras', 'Produção')  
                                                          AND [System.WorkItemType] IN ('Bug')
                                                          AND [System.State] <> 'Cancelado'
                                                          AND [Microsoft.VSTS.Common.ActivatedDate] >= '" + data_inicio.ToString("dd/MM/yyyy") + @"'
                                                          AND [Microsoft.VSTS.Common.ActivatedDate] <= '" + data_fim.ToString("dd/MM/yyyy") + @"'
                                                          AND [System.AreaPath] NOT UNDER 'SinaINFR'
                                                          AND [System.AssignedTo] <> 'Glaucia Arie Kuma'
                                                          AND [System.AssignedTo] <> 'Rogério Queiroz Maia'
                                                          AND [System.AssignedTo] <> 'Joao Roberto Magalhaes Lima' 
                                                     ORDER BY [System.Id]");

            List<Model.WorkItem> lista = new List<Model.WorkItem>();

            foreach (WorkItem workItem in itens)
            {
                Model.WorkItem demanda = new Model.WorkItem();
                demanda.ID = workItem.Id;
                demanda.AreaPath = workItem.AreaPath;
                demanda.AssignedTo = workItem.Fields.Contains("Assigned To") && workItem.Fields["Assigned To"].Value != null ? workItem.Fields["Assigned To"].Value.ToString() : "";
                demanda.WorkItemType = workItem.Type.Name;
                if (workItem.Fields.Contains("Start Date") && workItem.Fields["Start Date"].Value != null)
                    demanda.StartDate = (DateTime)workItem.Fields["Start Date"].Value;
                if (workItem.Fields.Contains("Finish Date") && workItem.Fields["Finish Date"].Value != null)
                    demanda.FinishDate = (DateTime)workItem.Fields["Finish Date"].Value;

                demanda.Title = workItem.Title;
                demanda.State = workItem.State;
                demanda.Rank = workItem.Fields["Rank"].Value.ToString();
                demanda.ChangedDate = workItem.ChangedDate;
                demanda.Remedy = workItem.Fields["Remedy"].Value != null ? workItem.Fields["Remedy"].Value.ToString() : "";
                demanda.TeamProject = workItem.Project.Name;
                demanda.CreatedBy = workItem.CreatedBy;
                demanda.CreatedDate = workItem.CreatedDate;

                demanda.FatorGerador = (workItem.Fields.Contains("Gerador") && workItem.Fields["Gerador"].Value != null ? workItem.Fields["Gerador"].Value.ToString() : string.Empty);

                if (workItem.Fields.Contains("Inicio Realizado") && workItem.Fields["Inicio Realizado"].Value != null)
                    demanda.StartDateRealized = (DateTime)workItem.Fields["Inicio Realizado"].Value;
                if (workItem.Fields.Contains("Termino Realizado") && workItem.Fields["Termino Realizado"].Value != null)
                    demanda.FinishDateRealized = (DateTime)workItem.Fields["Termino Realizado"].Value;

                if (workItem.Fields.Contains("Horas Previstas") && workItem.Fields["Horas Previstas"].Value != null)
                    demanda.HoursPlanned = Convert.ToDouble(workItem.Fields["Horas Previstas"].Value.ToString());

                if (workItem.Fields.Contains("Horas Realizadas") && workItem.Fields["Horas Realizadas"].Value != null)
                    demanda.HoursRealized = Convert.ToDouble(workItem.Fields["Horas Realizadas"].Value.ToString());

                foreach (Attachment attach in workItem.Attachments)
                {
                    if (attach.Name.ToLower().IndexOf("pe.txt") >= 0)
                        demanda.PendenciaExterna = true;
                }

                foreach (Revision rev in workItem.Revisions)
                {

                    if (rev.Fields["State"].Value.ToString().ToLower() == "codificacao")
                    {
                        demanda.CorrigidoPor = rev.Fields["Assigned To"].Value.ToString();

                    }

                    if (rev.Fields["Reason"].Value.ToString().ToLower() == "é um caso" ||
                        rev.Fields["Reason"].Value.ToString().ToLower() == "foi reproduzido" ||
                        rev.Fields["Reason"].Value.ToString().ToLower() == "não é duplicado" ||
                        rev.Fields["Reason"].Value.ToString().ToLower() == "não é obsoleto" ||
                        rev.Fields["Reason"].Value.ToString().ToLower() == "não foi corrigido" ||
                        rev.Fields["Reason"].Value.ToString().ToLower() == "não pode ser suspenso" ||
                        rev.Fields["Reason"].Value.ToString().ToLower() == "tem que ser corrigido")
                    {
                        demanda.NaoCorrigido = true;
                        demanda.DataNaoCorrigido = Convert.ToDateTime(rev.Fields["Changed Date"].Value);
                    }

                    if (rev.Fields["State"].Value.ToString().ToLower() == "cancelado")
                    {
                        demanda.Cancelado = true;
                        demanda.DataCancelamento = Convert.ToDateTime(rev.Fields["Changed Date"].Value);
                    }
                }

                lista.Add(demanda);
            }

            this.tfs.Dispose();
            return lista;
        }

        public List<Model.WorkItem> RetornaTaskDesenvMes(DateTime data_inicio, DateTime data_fim)
        {
            CDSBNetworkCredentials Credentials = new CDSBNetworkCredentials(Usuario, Senha);

            this.tfs = TeamFoundationServerFactory.GetServer(("http://utilcorp05:8080"), Credentials);
            this.WIStore = (WorkItemStore)tfs.GetService(typeof(WorkItemStore));

            WorkItemCollection itens = WIStore.Query(@"SELECT [System.Id], [System.Title], [System.AssignedTo], 
                                                              [Microsoft.VSTS.Scheduling.FinishDate],
                                                              [Bovespa.Sinacor.HorasPrevistas],
                                                              [Bovespa.Sinacor.HorasRealizadas],
                                                              [Microsoft.VSTS.Scheduling.StartDate], 
                                                              [Bovespa.Sinacor.TerminoRealizado],
                                                              [Bovespa.Sinacor.InicioRealizado],
                                                              [Microsoft.VSTS.Common.Priority]
                                                         FROM WorkItems
                                                        WHERE [System.WorkItemType] = 'TaskDesenv'
                                                          AND (( [Microsoft.VSTS.Scheduling.StartDate] >= '" + data_inicio.ToString("dd/MM/yyyy") + @"'
                                                                  AND [Microsoft.VSTS.Scheduling.StartDate] <= '" + data_fim.ToString("dd/MM/yyyy") + @"')
                                                           OR ( [Microsoft.VSTS.Scheduling.FinishDate] >= '" + data_inicio.ToString("dd/MM/yyyy") + @"'
                                                                  AND [Microsoft.VSTS.Scheduling.FinishDate] <= '" + data_fim.ToString("dd/MM/yyyy") + @"'))");

            List<Model.WorkItem> lista = new List<Model.WorkItem>();

            foreach (WorkItem workItem in itens)
            {
                Model.WorkItem demanda = new Model.WorkItem();
                demanda.ID = workItem.Id;
                demanda.AssignedTo = workItem.Fields.Contains("Assigned To") && workItem.Fields["Assigned To"].Value != null ? workItem.Fields["Assigned To"].Value.ToString() : "";
                demanda.WorkItemType = workItem.Type.Name;
                if (workItem.Fields.Contains("Start Date") && workItem.Fields["Start Date"].Value != null)
                    demanda.StartDate = (DateTime)workItem.Fields["Start Date"].Value;
                if (workItem.Fields.Contains("Finish Date") && workItem.Fields["Finish Date"].Value != null)
                    demanda.FinishDate = (DateTime)workItem.Fields["Finish Date"].Value;
                demanda.Title = workItem.Title;
                demanda.ChangedDate = workItem.ChangedDate;
                demanda.Remedy = workItem.Fields["Priority"].Value != null ? workItem.Fields["Priority"].Value.ToString() : "";
                demanda.Description = workItem.Description;
                demanda.CreatedBy = workItem.CreatedBy;
                demanda.CreatedDate = workItem.CreatedDate;

                foreach (Revision rev in workItem.Revisions)
                {
                    if (rev.Fields["State"].Value.ToString().ToLower() == "em codificacao" || rev.Fields["State"].Value.ToString().ToLower() == "em teste")
                    {
                        demanda.CorrigidoPor = rev.Fields["Assigned To"].Value.ToString();
                    }

                    if (rev.Fields["Reason"].Value.ToString().ToLower() == "é um caso" ||
                        rev.Fields["Reason"].Value.ToString().ToLower() == "foi reproduzido" ||
                        rev.Fields["Reason"].Value.ToString().ToLower() == "não é duplicado" ||
                        rev.Fields["Reason"].Value.ToString().ToLower() == "não é obsoleto" ||
                        rev.Fields["Reason"].Value.ToString().ToLower() == "não foi corrigido" ||
                        rev.Fields["Reason"].Value.ToString().ToLower() == "não pode ser suspenso" ||
                        rev.Fields["Reason"].Value.ToString().ToLower() == "tem que ser corrigido")
                    {
                        demanda.NaoCorrigido = true;
                        demanda.DataNaoCorrigido = Convert.ToDateTime(rev.Fields["Changed Date"].Value);
                    }

                    if (rev.Fields["State"].Value.ToString().ToLower() == "cancelado")
                    {
                        demanda.Cancelado = true;
                        demanda.DataCancelamento = Convert.ToDateTime(rev.Fields["Changed Date"].Value);
                    }
                }

                if (workItem.Fields.Contains("Inicio Realizado") && workItem.Fields["Inicio Realizado"].Value != null)
                    demanda.StartDateRealized = (DateTime)workItem.Fields["Inicio Realizado"].Value;
                if (workItem.Fields.Contains("Termino Realizado") && workItem.Fields["Termino Realizado"].Value != null)
                    demanda.FinishDateRealized = (DateTime)workItem.Fields["Termino Realizado"].Value;

                if (workItem.Fields.Contains("Horas Previstas") && workItem.Fields["Horas Previstas"].Value != null)
                    demanda.HoursPlanned = Convert.ToDouble(workItem.Fields["Horas Previstas"].Value.ToString());

                if (workItem.Fields.Contains("Horas Realizadas") && workItem.Fields["Horas Realizadas"].Value != null)
                    demanda.HoursRealized = Convert.ToDouble(workItem.Fields["Horas Realizadas"].Value.ToString());

                if (workItem.Fields.Contains("Remedy") && workItem.Fields["Remedy"].Value != null)
                    demanda.Remedy = workItem.Fields["Remedy"].Value.ToString();

                demanda.CreatedBy = workItem.CreatedBy;
                if (workItem.Fields.Contains("Tecnologia"))
                    demanda.Technology = workItem.Fields["Tecnologia"].Value.ToString();
                else
                    demanda.Technology = "Não definida";
                demanda.State = workItem.State;

                lista.Add(demanda);
            }

            this.tfs.Dispose();
            return lista;
        }

        public List<Model.WorkItem> RetornaTasksDia(string data_inicio)
        {
            CDSBNetworkCredentials Credentials = new CDSBNetworkCredentials(Usuario, Senha);

            this.tfs = TeamFoundationServerFactory.GetServer(("http://utilcorp05:8080"), Credentials);
            this.WIStore = (WorkItemStore)tfs.GetService(typeof(WorkItemStore));

            WorkItemCollection itens = WIStore.Query(@"SELECT [System.Id], [System.Title], [System.AssignedTo], 
            [Microsoft.VSTS.Scheduling.FinishDate], [Bovespa.Sinacor.HorasPrevistas], [Bovespa.Sinacor.HorasRealizadas], [Microsoft.VSTS.Scheduling.StartDate], 
            [Bovespa.Sinacor.TerminoRealizado], [Bovespa.Sinacor.InicioRealizado],  [Microsoft.VSTS.Common.Priority]
                  FROM WorkItems 
                        
                           WHERE [System.WorkItemType] = 'Task'  
                            AND ( [System.AssignedTo] = 'Joao Paulo Gregório Pontes'  OR  [System.AssignedTo] = 'Carlos Alberto Gonçalves Carvalho'  OR  [System.AssignedTo] = 'Marcelo Luis Prudente do Amaral') 
                            AND  [System.StartDate] >= '" + data_inicio + @"'
                            AND  [System.FinishDate] <= '" + data_inicio + @"'
                        ");

            List<Model.WorkItem> lista = new List<Model.WorkItem>();

            foreach (WorkItem workItem in itens)
            {
                Model.WorkItem demanda = new Model.WorkItem();
                demanda.ID = workItem.Id;
                demanda.AssignedTo = workItem.Fields.Contains("Assigned To") && workItem.Fields["Assigned To"].Value != null ? workItem.Fields["Assigned To"].Value.ToString() : "";
                demanda.WorkItemType = workItem.Type.Name;
                if (workItem.Fields.Contains("Start Date") && workItem.Fields["Start Date"].Value != null)
                    demanda.StartDate = (DateTime)workItem.Fields["Start Date"].Value;
                if (workItem.Fields.Contains("Finish Date") && workItem.Fields["Finish Date"].Value != null)
                    demanda.FinishDate = (DateTime)workItem.Fields["Finish Date"].Value;
                demanda.Title = workItem.Title;
                demanda.ChangedDate = workItem.ChangedDate;
                demanda.Remedy = workItem.Fields["Priority"].Value != null ? workItem.Fields["Priority"].Value.ToString() : "";
                demanda.Description = workItem.Description;
                demanda.CreatedBy = workItem.CreatedBy;
                demanda.CreatedDate = workItem.CreatedDate;

                if (workItem.Fields.Contains("Inicio Realizado") && workItem.Fields["Inicio Realizado"].Value != null)
                    demanda.StartDateRealized = (DateTime)workItem.Fields["Inicio Realizado"].Value;
                if (workItem.Fields.Contains("Termino Realizado") && workItem.Fields["Termino Realizado"].Value != null)
                    demanda.FinishDateRealized = (DateTime)workItem.Fields["Termino Realizado"].Value;

                if (workItem.Fields.Contains("Horas Previstas") && workItem.Fields["Horas Previstas"].Value != null)
                    demanda.HoursPlanned = Convert.ToDouble(workItem.Fields["Horas Previstas"].Value.ToString());

                if (workItem.Fields.Contains("Horas Realizadas") && workItem.Fields["Horas Realizadas"].Value != null)
                    demanda.HoursRealized = Convert.ToDouble(workItem.Fields["Horas Realizadas"].Value.ToString());

                if (workItem.Fields.Contains("Remedy") && workItem.Fields["Remedy"].Value != null)
                    demanda.Remedy = workItem.Fields["Remedy"].Value.ToString();

                demanda.CreatedBy = workItem.CreatedBy;
                if (workItem.Fields.Contains("Tecnologia"))
                    demanda.Technology = workItem.Fields["Tecnologia"].Value.ToString();
                else
                    demanda.Technology = "Não definida";
                demanda.State = workItem.State;

                lista.Add(demanda);
            }

            this.tfs.Dispose();
            return lista;
        }

        public List<Model.WorkItem> RetornaBugs()
        {
            CDSBNetworkCredentials Credentials = new CDSBNetworkCredentials(Usuario, Senha);

            this.tfs = TeamFoundationServerFactory.GetServer(("http://utilcorp05:8080"), Credentials);
            this.WIStore = (WorkItemStore)tfs.GetService(typeof(WorkItemStore));

            WorkItemCollection itens = WIStore.Query(@"SELECT [System.Id], [System.AreaPath], [System.State], [System.AssignedTo], [System.Title], 
                         [Microsoft.VSTS.Scheduling.StartDate], [Microsoft.VSTS.Scheduling.FinishDate], [Microsoft.VSTS.Common.Rank], [System.ChangedDate], [Bovespa.Sinacor.InicioRealizado],[Bovespa.Sinacor.TerminoRealizado],[Bovespa.Sinacor.Remedy],
                         [Bovespa.Sinacor.HorasPrevistas], [Bovespa.Sinacor.HorasRealizadas],[Bovespa.Sinacor.Tecnologia]
                  FROM WorkItems 
                        WHERE 
                            [System.WorkItemType] IN ('Bug')
                        AND [System.AssignedTo] IN ('Marcelo Luis Prudente do Amaral','Carlos Alberto Gonçalves Carvalho','Joao Paulo Gregório Pontes')
                        AND [Bovespa.Sinacor.Origem] IN ('Produção', 'Corretoras')
                        AND ( [System.State] NOT CONTAINS 'Integrado'  AND  [System.State] NOT CONTAINS 'Encerrado'  AND  [System.State] NOT CONTAINS 'Cancelado' )");

            List<Model.WorkItem> lista = new List<Model.WorkItem>();

            foreach (WorkItem workItem in itens)
            {
                Model.WorkItem demanda = new Model.WorkItem();
                demanda.ID = workItem.Id;
                demanda.AreaPath = workItem.AreaPath;
                demanda.AssignedTo = workItem.Fields.Contains("Assigned To") && workItem.Fields["Assigned To"].Value != null ? workItem.Fields["Assigned To"].Value.ToString() : "";
                if (workItem.Fields.Contains("Start Date") && workItem.Fields["Start Date"].Value != null)
                    demanda.StartDate = (DateTime)workItem.Fields["Start Date"].Value;
                if (workItem.Fields.Contains("Finish Date") && workItem.Fields["Finish Date"].Value != null)
                    demanda.FinishDate = (DateTime)workItem.Fields["Finish Date"].Value;

                demanda.Title = workItem.Title;
                demanda.State = workItem.State;
                demanda.Rank = workItem.Fields["Rank"].Value.ToString();
                demanda.ChangedDate = workItem.ChangedDate;

                if (workItem.Fields.Contains("Inicio Realizado") && workItem.Fields["Inicio Realizado"].Value != null)
                    demanda.StartDateRealized = (DateTime)workItem.Fields["Inicio Realizado"].Value;
                if (workItem.Fields.Contains("Termino Realizado") && workItem.Fields["Termino Realizado"].Value != null)
                    demanda.FinishDateRealized = (DateTime)workItem.Fields["Termino Realizado"].Value;

                if (workItem.Fields.Contains("Horas Previstas") && workItem.Fields["Horas Previstas"].Value != null)
                    demanda.HoursPlanned = Convert.ToDouble(workItem.Fields["Horas Previstas"].Value.ToString());

                if (workItem.Fields.Contains("Horas Realizadas") && workItem.Fields["Horas Realizadas"].Value != null)
                    demanda.HoursRealized = Convert.ToDouble(workItem.Fields["Horas Realizadas"].Value.ToString());

                if (workItem.Fields.Contains("Remedy") && workItem.Fields["Remedy"].Value != null)
                    demanda.Remedy = workItem.Fields["Remedy"].Value.ToString();

                demanda.CreatedBy = workItem.CreatedBy;
                if (workItem.Fields.Contains("Tecnologia"))
                    demanda.Technology = workItem.Fields["Tecnologia"].Value.ToString();
                else
                    demanda.Technology = "Não definida";

                demanda.Description = workItem.Description;
                demanda.CreatedDate = workItem.CreatedDate;


                lista.Add(demanda);
            }

            this.tfs.Dispose();
            return lista;
        }

        public string AtualizaTask(Model.WorkItem item)
        {
            CDSBNetworkCredentials Credentials = new CDSBNetworkCredentials(Usuario, Senha);

            this.tfs = TeamFoundationServerFactory.GetServer(("http://utilcorp05:8080"), Credentials);
            this.WIStore = (WorkItemStore)tfs.GetService(typeof(WorkItemStore));

            WorkItem item_TFS = this.WIStore.GetWorkItem(item.ID);

            item_TFS.Open();

            if (item.HoursPlanned != 0)
                item_TFS.Fields["Horas Previstas"].Value = item.HoursPlanned.ToString();

            if (item.HoursRealized != 0)
                item_TFS.Fields["Horas Realizadas"].Value = item.HoursRealized.ToString();

            if (item.StartDate != null && item.StartDate != DateTime.MinValue)
                item_TFS.Fields["Start Date"].Value = item.StartDate;

            if (item.FinishDate != null && item.FinishDate != DateTime.MinValue)
                item_TFS.Fields["Finish Date"].Value = item.FinishDate;

            if (item.FinishDateRealized != null && item.FinishDateRealized != DateTime.MinValue)
                item_TFS.Fields["Termino Realizado"].Value = item.FinishDateRealized;

            if (item.StartDateRealized != null && item.StartDateRealized != DateTime.MinValue)
                item_TFS.Fields["Inicio Realizado"].Value = item.StartDateRealized;

            item_TFS.State = item.State;
            item_TFS.Fields["Assigned To"].Value = item.AssignedTo;

            System.Collections.ArrayList lista = item_TFS.Validate();

            if (lista == null || lista.Count == 0)
            {
                item_TFS.Save();
            }

            string retorno = string.Empty;
            foreach (Field field in lista)
            {
                retorno += "Campo " + field.Name + " com erro:" + field.Status;
            }

            return retorno;
        }

        public string AtualizaWorkItem(Model.WorkItem item)
        {
            CDSBNetworkCredentials Credentials = new CDSBNetworkCredentials(Usuario, Senha);

            this.tfs = TeamFoundationServerFactory.GetServer(("http://utilcorp05:8080"), Credentials);
            this.WIStore = (WorkItemStore)tfs.GetService(typeof(WorkItemStore));

            WorkItem item_TFS = this.WIStore.GetWorkItem(item.ID);

            item_TFS.Open();

            if (item.HoursPlanned != 0)
                item_TFS.Fields["Horas Previstas"].Value = item.HoursPlanned.ToString();

            if (item.HoursRealized != 0)
                item_TFS.Fields["Horas Realizadas"].Value = item.HoursRealized.ToString();

            if (item.StartDate != null && item.StartDate != DateTime.MinValue)
                item_TFS.Fields["Start Date"].Value = item.StartDate;

            if (item.FinishDate != null && item.FinishDate != DateTime.MinValue)
                item_TFS.Fields["Finish Date"].Value = item.FinishDate;

            if (item.FinishDateRealized != null && item.FinishDateRealized != DateTime.MinValue)
                item_TFS.Fields["Termino Realizado"].Value = item.FinishDateRealized;

            if (item.StartDateRealized != null && item.StartDateRealized != DateTime.MinValue)
                item_TFS.Fields["Inicio Realizado"].Value = item.StartDateRealized;

            item_TFS.State = item.State;
            item_TFS.Fields["Assigned To"].Value = item.AssignedTo;

            System.Collections.ArrayList lista = item_TFS.Validate();

            if (lista == null || lista.Count == 0)
            {
                item_TFS.Save();
            }

            string retorno = string.Empty;
            foreach (Field field in lista)
            {
                retorno += "Campo " + field.Name + " com erro:" + field.Status;
            }

            return retorno;
        }

        public List<Model.WorkItem> RetornaWorkItensPorRemedy(List<int> numerosIncidentes) 
        {
            CDSBNetworkCredentials Credentials = new CDSBNetworkCredentials(Usuario, Senha);

            this.tfs = TeamFoundationServerFactory.GetServer(("http://utilcorp05:8080"), Credentials);
            this.WIStore = (WorkItemStore)tfs.GetService(typeof(WorkItemStore));

            WorkItemCollection itens = WIStore.Query(@"SELECT [System.WorkItemType], [System.Id], [System.AreaPath], [System.State], [System.AssignedTo], [System.Title], 
                         [Microsoft.VSTS.Scheduling.StartDate], [Microsoft.VSTS.Scheduling.FinishDate] FROM WorkItems WHERE [Microsoft.VSTS.Common.Priority] IN ("+RetornaArrayIncidentes(numerosIncidentes)+")  OR  [Bovespa.Sinacor.Remedy] IN ("+RetornaArrayIncidentes(numerosIncidentes)+") ORDER BY [System.Id]");
            

            List<Model.WorkItem> lista = new List<Model.WorkItem>();

            foreach (WorkItem workItem in itens)
            {
                Model.WorkItem demanda = new Model.WorkItem();
                demanda.ID = workItem.Id;
                demanda.AreaPath = workItem.AreaPath;
                demanda.AssignedTo = workItem.Fields.Contains("Assigned To") && workItem.Fields["Assigned To"].Value != null ? workItem.Fields["Assigned To"].Value.ToString() : "";
                if (workItem.Fields.Contains("Start Date") && workItem.Fields["Start Date"].Value != null)
                    demanda.StartDate = (DateTime)workItem.Fields["Start Date"].Value;
                if (workItem.Fields.Contains("Finish Date") && workItem.Fields["Finish Date"].Value != null)
                    demanda.FinishDate = (DateTime)workItem.Fields["Finish Date"].Value;

                demanda.Title = workItem.Title;
                demanda.State = workItem.State;

                if (workItem.Fields.Contains("Inicio Realizado") && workItem.Fields["Inicio Realizado"].Value != null)
                    demanda.StartDateRealized = (DateTime)workItem.Fields["Inicio Realizado"].Value;
                if (workItem.Fields.Contains("Termino Realizado") && workItem.Fields["Termino Realizado"].Value != null)
                    demanda.FinishDateRealized = (DateTime)workItem.Fields["Termino Realizado"].Value;

                if (workItem.Fields.Contains("Remedy") && workItem.Fields["Remedy"].Value != null)
                    demanda.Remedy = workItem.Fields["Remedy"].Value.ToString();

                if(workItem.Fields.Contains("Priority") && workItem.Fields["Priority"].Value != null)
                    demanda.Remedy = workItem.Fields["Priority"].Value.ToString();

                demanda.WorkItemType = workItem.Type.Name;
                demanda.CreatedBy = workItem.CreatedBy;


                lista.Add(demanda);
            }

            this.tfs.Dispose();
            return lista;
        }

        private string RetornaArrayIncidentes(List<int> numerosIncidentes)
        {
 	        string retorno = string.Empty;

            foreach(int numero in numerosIncidentes)
            {
                retorno += "'" + numero + "',";
            }

            return retorno.TrimEnd(',');
        }

        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            //this.tfs.Dispose();
        }

        #endregion
    }
}
